﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using WFBASE;
using WFDAL;

namespace WFBUS.Privilage
{
    public class Users
    {
        /// <summary>
        /// 查詢系統中的用戶
        /// </summary>
        /// <param name="factNo"></param>
        /// <param name="deptNo"></param>
        /// <param name="userName"></param>
        /// <param name="loginName"></param>
        /// <returns></returns>
        public string GetUsers(string factNo, string deptNo, string userName, string loginName)
        {
            string sql = "select um.*,f.fact_nm,d.dept_nm from wf_userm um,wf_factm f,wf_dept d " +
                "WHERE um.fact_no=f.fact_no(+) AND um.dept_no=d.dept_no(+) ";
            sql += Util.AddEQSqlParam("um.FACT_NO", factNo, " and ");
            sql += Util.AddLikeSqlParam("d.dept_nm", deptNo, " and ");
            sql += Util.AddLikeSqlParam("um.user_name", userName, " and ");
            sql += Util.AddLikeSqlParam("um.login_name", loginName, "and");
            //DataTable dt = new OracleCommand().QueryByWhere("wf_userm", sql);
            //return NewConverter.DataTableToJson(dt);
            DataTable dt = new OracleCommand().Query(sql);
            return WFBASE.NewConverter.DataTableToJson(dt);
        }

        /// <summary>
        /// 啟用停用用戶
        /// </summary>
        /// <param name="p_userId"></param>
        /// <param name="p_action"></param>
        /// <returns></returns>
        public string StopUser(string p_userId, string p_action)
        {
            string sql = "";
            if (p_action == "N")//启动
                sql = "update userm set stop_mk='Y' where user_id='" + p_userId.Replace("'", "''") + "'";
            else
                sql = "update userm set stop_mk='N' where user_id='" + p_userId.Replace("'", "''") + "'";
            if (new OracleCommand().ExecuteSQL(sql))
                return "Y";
            return "更新用户状态发生异常,请联络系统管理员查核!";

        }

        public DataSet GetUserInfo(string p_userid)
        {
            string Sql = "SELECT U.*,F.C_FACT_NM AS FACT_NM,D.C_DEPT_NM AS DEPT_NM,ag.c_guide_nm as POLICYNAME,UP.POLICYVAL,POLICYVAL2,POLICYVAL3 " +
                                "FROM USERM U,FACTM F,DEPTEAM D,Allguid ag,Userpolicy up " +
                                "WHERE U.FACT_NO=F.C_FACT_NO(+) AND U.DEPT_NO=D.C_DEPT_NO(+) AND u.Assistant=ag.c_guide_id(+) " +
                                " and u.user_id=up.user_id(+) AND U.user_id='" + p_userid.Trim().Replace("'", "''") + "'";
            return new OracleCommand().QueryDataSet(Sql);
        }
        public string InsUser(string p_factNo, string p_deptNo, string p_userName, string p_loginName, string p_password,
            string p_position, string p_tel, string p_mail, string p_yz_mode, string p_yz_text, string p_yz_text2, string p_yz_text3)
        {
            string sql = "insert into userm(fact_no,dept_no,login_name,user_name,user_pwd,user_email,user_tel,position,YN_sysuser,stop_mk,Assistant) " +
                "values('" + p_factNo + "','" + p_deptNo + "','" + p_loginName.Replace("'", "''") + "','" + p_userName.Replace("'", "''") + "','" +
                p_password.Replace("'", "''") + "','" + p_mail.Replace("'", "''") + "','" + p_tel + "','" + p_position.Replace("'", "''") +
                "','Y','N','" + p_yz_mode + "')";
            OracleCommand cmd = new OracleCommand();
            if (cmd.ExecuteSQL(sql))
            {
                cmd.ExecuteSQL("INSERT INTO members(rule_id,user_id) select '1800010',user_id from userm where login_name='" + p_loginName.Replace("'", "''") + "'");
                cmd.ExecuteSQL("INSERT INTO Userpolicy(User_Id,policy,Policyval,Policyval2,Policyval3) SELECT user_id,'" + p_yz_mode + "','" + p_yz_text + "','" + p_yz_text2 + "','" + p_yz_text3 + "' FROM userm WHERE login_name='" + p_loginName.Replace("'", "''") + "'");
                return "Y";
            }
            new SendMail().WriteLogs("新增用户发生错误：" + sql);
            return "新增用户发生异常，请联络系统管理员查核!";
        }
        public string UpdUser(string p_userId, string p_factNo, string p_deptNo, string p_userName, string p_loginName, string p_password,
            string p_position, string p_tel, string p_mail, string p_yz_mode, string p_yz_text, string p_yz_text2, string p_yz_text3)
        {
            string sql = "update userm set fact_no='" + p_factNo + "',dept_no='" + p_deptNo + "',user_name='" + p_userName.Replace("'", "''") + "'," +
                "user_email='" + p_mail.Replace("'", "''") + "',user_tel='" + p_tel.Replace("'", "''") +
                "',position='" + p_position.Replace("'", "''") + "',Assistant='" + p_yz_mode + "' where user_id='" + p_userId + "'";
            OracleCommand cmd = new OracleCommand();
            if (cmd.ExecuteSQL(sql))
            {
                cmd.ExecuteSQL("delete userpolicy WHERE user_id='" + p_userId + "'");
                cmd.ExecuteSQL("INSERT INTO Userpolicy(User_Id,policy,Policyval,Policyval2,Policyval3) SELECT user_id,'" + p_yz_mode + "','" + p_yz_text + "','" + p_yz_text2 + "','" + p_yz_text3 + "' FROM userm WHERE user_id='" + p_userId + "'");
                //this.ExecuteSQL("update Userpolicy set policy='" + p_yz_mode + "',Policyval='" + p_yz_text + "',Policyval2='" + p_yz_text2 + "',Policyval3='" + p_yz_text3 + "' where user_id='" + p_userId + "'");
                return "Y";
            }
            //new SendMail().WriteLogs("新增用户发生错误：" + sql);
            return "新增用户发生异常，请联络系统管理员查核!";
        }

    }
}
